package com.cjq.dao.Impl;

import com.cjq.bean.Express;
import com.cjq.dao.BaseExpressDao;
import com.cjq.exception.CodeException;
import com.cjq.util.DBUtils;

import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class ExpressDaoMysql extends DBUtils implements BaseExpressDao {

    //用于查询数据库中的全部快递（总数+新增），待取件快递（总数+新增）
    public static final String sql_console = "select count(id) data1_size,count(TO_DAYS(intime)=TO_DAYS(NOW()) or null) data1_day,count(status=0 or null) data2_size,count(TO_DAYS(intime)=TO_DAYS(NOW()) and status=0 or null) data2_day from express";
    //查询所有快递信息
    public static final String sql_findAll = "select * from express ";
    //分页查询快递信息
    public static final String sql_findAll_limit = "select * from express limit ?,?";
    //通过快递单号查询快递信息
    public static final String sql_findByNumber= "select * from express where number=?";
    //通过取件码查询快递信息
    public static final String sql_findByCode = "select * from express where code=?";
    //通过用户电话查询快递信息
    public static final String sql_findByUserPhone = "select * from express where userphone=?";
    //通过录入人的电话查询快递信息
    public static final String sql_findBySysPhone = "select * from express where sysphone=?";
    //新增快递 录入快递
    public static final String sql_insert = "insert into express (number,username,userphone,company,code,intime,status,sysphone) values(?,?,?,?,?,NOW(),0,?)";
    //快递修改
    public static final String sql_update = "update express set number=?,username=?,company=?,status=? where id =?";
    //快递删除
    public static final String sql_delete = "delete from express where id = ?";
    //取快递，修改状态码
    public static final String sql_updateStatus = "update express set status = 1 ,outtime = NOW(),code = null where code=?";
    //通过录入人的电话查询快递状态为未取件的快递
    public static final String sql_findBySysPhoneAndStatus = "select * from express where userphone=? and status = 0";


    /**
     * 用于查询数据库中的全部快递（总数+新增），待取件快递（总数+新增）
     *
     * @return [{size:总数，day:新增},{size:总数,day:新增}]    返回一个Map类型的List集合
     */
    @Override
    public List<Map<String, Integer>> console() {
        List<Map<String,Integer>> data = new ArrayList<>();
        try {
            ResultSet rs = query(sql_console, null);
            if (rs.next()){
                int data1_size = rs.getInt("data1_size");
                int data1_day = rs.getInt("data1_day");
                int data2_size = rs.getInt("data2_size");
                int data2_day = rs.getInt("data2_day");
                Map data1 = new HashMap();

                data1.put("data1_size",data1_size);

                data1.put("data1_day",data1_day);
                Map data2 = new HashMap();
                data2.put("data2_size",data2_size);
                data2.put("data2_day",data2_day);

                data.add(data1);
                data.add(data2);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            closeall();
        }

        return data;
    }

    /**
     * 用户查询所有快递
     *
     * @param limit      是否分页的标记  默认是true 表示分页   false表示查询所有
     * @param offset     起始条数 起始索引
     * @param pageNumber 每页查询的条数
     * @return 快递的集合
     */
    @Override
    public List<Express> findAll(boolean limit, int offset, int pageNumber) {

        List<Express> data = new ArrayList<>();
        ResultSet rs = null;


        try {
            if (limit){   //limit为true表示为true   表示分页查询
                List params = new ArrayList();
                params.add(offset);
                params.add(pageNumber);
                rs = query(sql_findAll_limit,params);
            }else{
                rs = query(sql_findAll, null);
            }


            ResultSetMetaData metaData = rs.getMetaData();     //存储结果集信息
            int columnCount = metaData.getColumnCount(); //得到列数
            String[] columnNames = new String[columnCount];  //存放结果的列名
            for (int i = 0; i < columnCount; i++) {
                columnNames[i] = metaData.getColumnName(i + 1);
            }

            //得到实体类中的所有方法  获取bean中的Student类的所有方法
            Method[] methods = Express.class.getDeclaredMethods();
            while (rs.next()) {
                Express express = new Express();
                for (String columnname : columnNames) {
                    String name = "set" + columnname;
                    for (Method method : methods) {
                        if (method.getName().equalsIgnoreCase(name)) {
                            method.invoke(express, rs.getObject(columnname));//执行了对应的set方法
                            break;
                        }
                    }
                }
                data.add(express);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        } catch (InvocationTargetException e) {
            e.printStackTrace();
        } finally {
            closeall();
        }

        return data;
    }

    /**
     * 根据快递单号，查询快递信息
     *
     * @param number 单号
     * @return 查询快递信息，单号不存在时返回null
     */
    @Override
    public Express findByNumber(String number) {
        Express express = null;
        List params = new ArrayList();
        params.add(number);
        ResultSet rs = query(sql_findByNumber, params);

        try {
            ResultSetMetaData metaData = rs.getMetaData();     //存储结果集信息
            int columnCount = metaData.getColumnCount(); //得到列数
            String[] columnNames = new String[columnCount];  //存放结果的列名
            for (int i = 0; i < columnCount; i++) {
                columnNames[i] = metaData.getColumnName(i + 1);
            }

            //得到实体类中的所有方法  获取bean中的Student类的所有方法
            Method[] methods = Express.class.getDeclaredMethods();
            while (rs.next()) {
                express = new Express();
                for (String columnname : columnNames) {
                    String name = "set" + columnname;
                    for (Method method : methods) {
                        if (method.getName().equalsIgnoreCase(name)) {
                            method.invoke(express, rs.getObject(columnname));//执行了对应的set方法
                            break;
                        }
                    }
                }
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        } catch (InvocationTargetException e) {
            e.printStackTrace();
        } finally {
            closeall();
        }

        return express;
    }

    /**
     * 根据快递单号，查询快递信息
     *
     * @param code 取件码
     * @return 查询快递信息，取件码不存在时返回null
     */
    @Override
    public Express findByCode(String code) {
        Express express = null;
        List params = new ArrayList();
        params.add(code);
        ResultSet rs = query(sql_findByCode, params);

        try {
            ResultSetMetaData metaData = rs.getMetaData();     //存储结果集信息
            int columnCount = metaData.getColumnCount(); //得到列数
            String[] columnNames = new String[columnCount];  //存放结果的列名
            for (int i = 0; i < columnCount; i++) {
                columnNames[i] = metaData.getColumnName(i + 1);
            }

            //得到实体类中的所有方法  获取bean中的Student类的所有方法
            Method[] methods = Express.class.getDeclaredMethods();
            while (rs.next()) {
                express = new Express();
                for (String columnname : columnNames) {
                    String name = "set" + columnname;
                    for (Method method : methods) {
                        if (method.getName().equalsIgnoreCase(name)) {
                            method.invoke(express, rs.getObject(columnname));//执行了对应的set方法
                            break;
                        }
                    }
                }
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        } catch (InvocationTargetException e) {
            e.printStackTrace();
        } finally {
            closeall();
        }

        return express;
    }

    /**
     * 根据用户手机号码，查询快递信息 一个用户可以有多个快递
     *
     * @param userPhone 用户手机号码
     * @return 查询快递信息，电话号码不存在时返回null
     */
    @Override
    public List<Express> findByUserPhone(String userPhone) {
        List<Express> data = new ArrayList<>();
        List params = new ArrayList();
        params.add(userPhone);
        ResultSet rs = query(sql_findByUserPhone,params);

        try {
            ResultSetMetaData metaData = rs.getMetaData();     //存储结果集信息
            int columnCount = metaData.getColumnCount(); //得到列数
            String[] columnNames = new String[columnCount];  //存放结果的列名
            for (int i = 0; i < columnCount; i++) {
                columnNames[i] = metaData.getColumnName(i + 1);
            }

            //得到实体类中的所有方法  获取bean中的Student类的所有方法
            Method[] methods = Express.class.getDeclaredMethods();
            while (rs.next()) {
                Express express = new Express();
                for (String columnname : columnNames) {
                    String name = "set" + columnname;
                    for (Method method : methods) {
                        if (method.getName().equalsIgnoreCase(name)) {
                            method.invoke(express, rs.getObject(columnname));//执行了对应的set方法
                            break;
                        }
                    }
                }
                data.add(express);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        } catch (InvocationTargetException e) {
            e.printStackTrace();
        } finally {
            closeall();
        }

        return data;
    }

    /**
     * 根据录入人手机号码，查询录入人所录入的所有记录
     *
     * @param sysPhone 录入人的到手机号码
     * @return
     */
    @Override
    public List<Express> findBySysPhone(String sysPhone) {
        List<Express> data = new ArrayList<>();
        List params = new ArrayList();
        params.add(sysPhone);
        ResultSet rs = query(sql_findBySysPhone,params);

        try {
            ResultSetMetaData metaData = rs.getMetaData();     //存储结果集信息
            int columnCount = metaData.getColumnCount(); //得到列数
            String[] columnNames = new String[columnCount];  //存放结果的列名
            for (int i = 0; i < columnCount; i++) {
                columnNames[i] = metaData.getColumnName(i + 1);
            }

            //得到实体类中的所有方法  获取bean中的Student类的所有方法
            Method[] methods = Express.class.getDeclaredMethods();
            while (rs.next()) {
                Express express = new Express();
                for (String columnname : columnNames) {
                    String name = "set" + columnname;
                    for (Method method : methods) {
                        if (method.getName().equalsIgnoreCase(name)) {
                            method.invoke(express, rs.getObject(columnname));//执行了对应的set方法
                            break;
                        }
                    }
                }
                data.add(express);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        } catch (InvocationTargetException e) {
            e.printStackTrace();
        } finally {
            closeall();
        }

        return data;
    }

    /**
     * 快递的录入
     *
     * @param e 录入的快递对象
     * @return 返回boolean类型，true表示成功
     * insert into express (number,username,userphone,company,code,intime,status,sysphone) values(?,?,?,?,?,NOW(),0,?)"
     */
    @Override
    public boolean insert(Express e) throws  CodeException{    //如果外部有调用到这个方法就需要接受到这个异常，进行处理

        try {
            List params = new ArrayList();
            params.add(e.getNumber());
            params.add(e.getUserName());
            params.add(e.getUserPhone());
            params.add(e.getCompany());
            params.add(e.getCode());
            params.add(e.getSysPhone());
            getPps(sql_insert);
            setParams(params);
            return pps.executeUpdate()>0?true:false;

        } catch (Exception e1) {
            if (e1.getMessage().contains("for key 'express.code'")){     //如果是因为取件码重复而引起的错误，就走自定义的异常处理
                CodeException e2 = new CodeException(e1.getMessage());
                throw e2;

            }else {
                e1.printStackTrace();
            }

        } finally {
            closeall();
        }
        return false;

    }

    /**
     * 快递的修改
     *
     * @param id
     * @param newExpress 新的快递对象包括（number,company,userName,status）
     * @return
     *  "update express set number=?,username=?,company=?,status=? where id = ?";
     */
    @Override
    public boolean update(int id, Express newExpress) {
        List params = new ArrayList();
        try {
            params.add(newExpress.getNumber());
            params.add(newExpress.getUserName());
            params.add(newExpress.getCompany());
            params.add(newExpress.getStatus());
            params.add(id);

             return update(sql_update,params)>0?true:false;
        } catch (Exception e) {
            System.out.println(e);
        } finally {
            closeall();
        }

        return false;
    }

    @Override
    public boolean delete(int id) {
        List params = new ArrayList();
        try {
            params.add(id);

            return update(sql_delete,params)>0?true:false;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            closeall();
        }

        return false;
    }


    @Override
    public boolean updateStatus(String code) {



        List params = new ArrayList();
        try {

            params.add(code);

            return update(sql_updateStatus,params)>0?true:false;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            closeall();
        }

        return false;
    }


    @Override
    public List<Express> findBySysPhoneAndStatus(String sysPhone) {
        List<Express> data = new ArrayList<>();
        List params = new ArrayList();
        params.add(sysPhone);
        ResultSet rs = query(sql_findBySysPhoneAndStatus,params);

        try {
            ResultSetMetaData metaData = rs.getMetaData();     //存储结果集信息
            int columnCount = metaData.getColumnCount(); //得到列数
            String[] columnNames = new String[columnCount];  //存放结果的列名
            for (int i = 0; i < columnCount; i++) {
                columnNames[i] = metaData.getColumnName(i + 1);
            }

            //得到实体类中的所有方法  获取bean中的Student类的所有方法
            Method[] methods = Express.class.getDeclaredMethods();
            while (rs.next()) {
                Express express = new Express();
                for (String columnname : columnNames) {
                    String name = "set" + columnname;
                    for (Method method : methods) {
                        if (method.getName().equalsIgnoreCase(name)) {
                            method.invoke(express, rs.getObject(columnname));//执行了对应的set方法
                            break;
                        }
                    }
                }
                data.add(express);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        } catch (InvocationTargetException e) {
            e.printStackTrace();
        } finally {
            closeall();
        }

        return data;
    }
}
